mysql 复习

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 24k | 阅读时长 ≈ 22 分钟

目录：/MySQL实战45讲 [161.8M]
┣━━01讲基础架构：一条SQL查询语句是如何执行的.html [54.2K]
┣━━02讲日志系统：一条SQL更新语句是如何执行的.html [65K]
┣━━03讲事务隔离：为什么你改了我还看不见.html [56.8K]
┣━━04讲深入浅出索引（上）.html [59.9K]
┣━━05讲深入浅出索引（下）.html [63.2K]
┣━━06讲全局锁和表锁：给表加个字段怎么有这么多阻碍.html [63.9K]
┣━━07讲行锁功过：怎么减少行锁对性能的影响.html [60.9K]
┣━━08讲事务到底是隔离的还是不隔离的.html [69K]
┣━━09讲普通索引和唯一索引，应该怎么选择.html [70K]
┣━━10讲MySQL为什么有时候会选错索引.html [67.1K]
┣━━11讲怎么给字符串字段加索引.html [54.6K]
┣━━12讲为什么我的MySQL会“抖”一下.html [62K]
┣━━13讲为什么表数据删掉一半，表文件大小不变.html [60.2K]
┣━━14讲count这么慢，我该怎么办.html [64.2K]
┣━━15讲答疑文章（一）：日志和索引相关问题.html [73.4K]
┣━━16讲“orderby”是怎么工作的.html [74.3K]
┣━━17讲如何正确地显示随机消息.html [63.5K]
┣━━18讲为什么这些SQL语句逻辑相同，性能却差异巨大.html [62.1K]
┣━━19讲为什么我只查一行的语句，也执行这么慢.html [59.6K]
┣━━20讲幻读是什么，幻读有什么问题.html [67.2K]
┣━━21讲为什么我只改一行的语句，锁这么多.html [68.7K]
┣━━22讲MySQL有哪些“饮鸩止渴”提高性能的方法.html [75.2K]
┣━━23讲MySQL是怎么保证数据不丢的.html [65.4K]
┣━━24讲MySQL是怎么保证主备一致的.html [66.4K]
┣━━25讲MySQL是怎么保证高可用的.html [68.4K]
┣━━26讲备库为什么会延迟好几个小时.html [67.5K]
┣━━27讲主库出问题了，从库怎么办.html [63.3K]
┣━━28讲读写分离有哪些坑.html [67.1K]
┣━━29讲如何判断一个数据库是不是出问题了.html [49.4K]
┣━━30讲答疑文章（二）：用动态的观点看加锁.html [38.9K]
┣━━31讲误删数据后除了跑路，还能怎么办.html [65.1K]
┣━━32讲为什么还有kill不掉的语句.html [59.4K]
┣━━33讲我查这么多数据，会不会把数据库内存打爆.html [61.1K]
┣━━34讲到底可不可以使用join.html [37.2K]
┣━━35讲join语句怎么优化.html [67.4K]
┣━━36讲为什么临时表可以重名.html [55.3K]
┣━━37讲什么时候会使用内部临时表.html [57.2K]
┣━━38讲都说InnoDB好，那还要不要使用Memory引擎.html [52.1K]
┣━━39讲自增主键为什么不是连续的.html [50.6K]
┣━━40讲insert语句的锁为什么这么多.html [53.8K]
┣━━41讲怎么最快地复制一张表.html [34K]
┣━━42讲grant之后要跟着flushprivileges吗.html [51.4K]
┣━━43讲要不要使用分区表.html [51.2K]
┣━━44讲答疑文章（三）：说一说这些好问题.html [53.1K]
┣━━开篇词讲这一次，让我们一起来搞懂MySQL.html [40.6K]
┗━━直播回顾讲林晓斌：我的MySQL心路历程.html [51.7K]

1. 一条语句的执行过程

以查询语句为例，一条查询语句要经过以下几个步骤：

  1. 连接器。首先会在 Server 端验证连接的正确性，判断用户名密码是否正确，进行连接管理；
  2. 查询缓存。这个在 Mysql 8.0 之后就没有了，因为缓存命中率一向很低，只要修改了表就会导致缓存的失效；
  3. 分析器。主要进行词法语法的分析，判断语句是否符合规范，语句中的表以及字段在表中是否正确；
  4. 优化器。对查询语句进行优化，主要是索引的选择；
  5. 执行器。这一步才真正开始打开表来操作，需要首先验证是否可以有操作表的权限，然后调用InnoDB 等存储引擎的读写接口来完成语句的操作，最后返回结果；
  6. 存储引擎。主要负责存储数据以及提供数据读写的接口。

image-20200313164342777

2. 讲日志系统：一条 SQL 更新语句的执行

「MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘」

InnoDB中特有的日志 redo-log：

  • 在 InnoDB 中可以保证即使数据库发生了重启，之前提交的记录并不会丢失，这个能力称为 “crash-safe”「提供崩溃恢复功能」；
  • 是物理日志，记录的是“这个数据页上做了什么改变”，与存储引擎的作用相符，主要是处理存储方面的功能；
  • 是循环写的，空间固定会用完的。用完了就把之前的擦除，然后继续写，用两个指针来控制。

Server 层独有的日志 binlog：

  • 是 Server 层实现的，所有引擎都可以使用；
  • 作用是用于归档，记录的是“做了什么逻辑操作”，重点在执行逻辑上，不在存储，因为 Server 就是负责 Mysql 功能方面；
  • 是可以追加写的，写完一页换一页，不会擦除以前的。

mysql> update T set c=c+1 where ID=2;

上述的 update 语句执行顺序大体和 1 中一样，具体的执行器的流程如下：

  1. 执行器先找引擎，获取 ID = 2 这一行，ID 是主键，引擎会直接用索引树定位到这一行的数据，如果这一行的数据在内存中，那直接返回就好了，如果不在，则需要去磁盘中读取并加载到内存中，然后返回给执行器；
  2. 执行器拿到了这一行的数据，将 c 自加一，然后调用引擎去写入数据；
  3. 引擎会先将这个数据加载到内存中，并且去写 redo-log，此时注意，redo-log 并未提交，而是处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务；
  4. 执行器收到了存储引擎的消息后，就开始写入 bin-log，并把 bin-log写入磁盘；
  5. 执行器写入完毕后，调用引擎的提交事务接口，引擎把刚刚写入的 redo-log 改成 commit 状态，提交事务。

上述值得注意的是， redo-log 是 两阶段提交，也就是 先 prepare 再 commit。之所以这么做的原因是为了让两份日志之间的逻辑一致。

要注意，现阶段的数据恢复还是靠着 binlog + 整库备份去完成的，按理说 redo-log 也能完成而且速度肯定要比 bin-log 快得多，但是由于 redo-log 是会擦除的，不具备bin-log 的归档功能，所以现阶段还是没办法。

思考题：定期全量备份的周期“取决于系统重要性，有的是一天一备，有的是一周一备”。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

一天一备：好处是“最长恢复时间”更短。在一天一备的模式里，最坏情况下需要应用一天的binlog。比如，你每天0点做一次全量备份，而要恢复出一个到昨天晚上23点的备份。但是坏处也是显而易见的，就是你需要大量的存储。

一周一备最坏情况就要应用一周的binlog了。这个恢复时间长，但是明显存储的消耗就小得多了。

3. 事务隔离

事务的四大特性：

  1. 原子性。要么不做，要么全做；
  2. 一致性。在处理事务的过程中，保证数据库状态和其他业务规则保持一致，比如转账，前后两者的账湖总额不变；
  3. 隔离性。不同事务之间互不影响；
  4. 持久性。事务提交之后需要持久化到数据库中去，即使崩溃，也能恢复。

事务的隔离级别：

  1. 读未提交。别人还没提交的东西，就能读取到，会产生幻读、不可重复读、脏读的问题；「一个事务还没提交时，它做的变更就能被别的事务看到。」
  2. 读提交。别人提交的东西，就能读取到。会产生幻读、不可重复读；「一个事务提交之后，它做的变更才会被其他事务看到。」
  3. 可重复读。在事务执行期间，别人就算提交了东西并改动了，当前事务看不到，不会去读最新的，还是可能会产生幻读的情况；「一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。」
  4. 序列化。加锁，牛逼。「顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。」

其中，最难理解的就是可重复读的隔离级别是如何实现的，这也是mysql的默认隔离级别：

就是使用的回滚技术，在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。「由undo log完成的」

image-20200313180011653

如图所示，这也是 数据库的多版本并发控制的基础，采用回滚机制。当然了，回滚日志不能一直保留，所以在必要的时候删除，删除的时间点就是当没有事务再需要用到这些回滚日志时，回滚日志会被删除。所以我们最好不要使用长事务，这样会导致回滚的数据很多很多。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

如何避免长事务对业务的影响？

这个问题，我们可以从应用开发端和数据库端来看。

首先，从应用开发端来看：

  1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

其次，从数据库端来看：

  1. 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；
  2. Percona的pt-kill这个工具不错，推荐使用；
  3. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
  4. 如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

4. 深入浅出索引（上）

为何不使用 哈希表、数组、B 树、二叉搜索树，而采用了 B+？

  1. 为何不用哈希表？

    哈希表进行等值查询的确很快，O(1) 的时间复杂度，但是其进行范围查询就必须遍历全表，并且如果相同的值太多，就容易导致哈希冲突，性能进一步下降。

  2. 为何不用数组？

    数组的确可以很快的进行等值查询和范围查询，但是其更新很麻烦，如果中间需要删除，那就要移动后面所有数据，所以只适用于静态存储引擎。

  3. 为何不用 B 树？

    B 树最大的问题就是非叶子节点有一个 Data 域，我们知道数据库的性能主要取决于磁盘 I/O 的次数，mysql 采用了磁盘预读的机制，也就是每一个树的节点都申请一页的存储空间，这样方便读写，就是因为 B 树存储了 Data 域，相比之下肯定就只能存更少的指针域了，这样就会导致整个树的高度拉高，磁盘 I/O 次数增加，二叉搜索树同样如此，树高过高导致磁盘 I/O 次数增加。

  4. 为何用 B+ 树？

    1. 我觉得第一点就是因为其树低，每个非叶子节点只存储了指针域和主键（这里说的是聚集索引）；
    2. 叶子节点有链表，方便范围查询；
    3. 查询效率很稳定。

为何最好使用自增主键？

因为这样就可以减少维护索引有序性带来的开销，如果不采用自增主键，那么在插入的时候可能会在中间，造成一个页分裂的过程，开销很大，会导致存储的碎片化。

5. 深入浅出索引（下）

  1. 什么叫回表？

    普通索引在找到主键后，回到主键索引树搜索的过程，称为回表。

  2. 如何减少回表的次数呢？

    1. 覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。其实就是联合索引，然后符合覆盖的要求就称之为覆盖索引，就是多个字段拼成一个索引，然后对应主键的映射。

    2. 最左前缀规则。只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

      基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。

      这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。那如果既有联合索引，又有基于 a、b 各自的索引呢？那就考虑空间问题了，如果 a 的字段明显比 b 的长，则一页下可以容纳更多的 b，那肯定就创建一个（a,b)的联合索引和一个(b)的单字段索引。

    3. 索引下推。在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

      而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

6. 全局锁和表级锁

根据加锁范围：MySQL里面的锁可以分为：全局锁、表级锁、行级锁

一、全局锁：
对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)

FTWRL的启动时间点：

  • FTWRL 前有读写的话 ,FTWRL 都会等待 读写执行完毕后才执行；
  • FTWRL 执行的时候要刷脏页的数据到磁盘,因为要保持数据的一致性 ，理解的执行FTWRL时候是 所有事务 都提交完毕的时候。

这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
二、表级锁
MySQL里面表级锁有两种，一种是表锁，一种是元数据锁(meta data lock,MDL)
表锁的语法是:lock tables … read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。Server 层面实现的！！！！
MDL的作用：保证读写的正确性。
在对一个表做增删改查（DML）操作的时候，加MDL读锁；当要对表做结构变更（DDL）操作的时候，加MDL写锁。
读锁之间不互斥。因为有隔离级别在，mvcc，所以读锁之间无需复制即可，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。所以小心在给小表做 DDL 时把这个库搞崩。

数据库四种语言：

  1. DDL （Data Definition Language）：数据定义语言，无需提交就可以执行的，主要有 CREATE、ALTER、DROP等等；
  2. DML（Data Manipulation Language）：数据操作语言，这个也是需要提交事务的哦，INSERT、UPDATE、DELETE、TRUNCATE、SELECT、以及 LOCK TABLE、CALL、EXPLAIN PLAN
  3. DCL（Data Control Language）：数据控制语言，包括 GRANT、REVOKE
  4. TCL（Transaction Control Language）：事务控制语言，包括 ROLLBACK、COMMIT、SAVEPOINT、SET TRANSACTION。

今日思考题：备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个DDL是针对表t1的， 这里我把备份过程中几个关键的语句列出来：

1
2
3
4
5
6
7
8
9
10
11
12
13
>Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
>/* other tables */
>Q3:SAVEPOINT sp;
>/* 时刻 1 */
>Q4:show create table `t1`;
>/* 时刻 2 */
>Q5:SELECT * FROM `t1`;
>/* 时刻 3 */
>Q6:ROLLBACK TO SAVEPOINT sp;
>/* 时刻 4 */
>/* other tables */
>

在备份开始的时候，为了确保RR（可重复读）隔离级别，再设置一次RR隔离级别(Q1);

启动事务，这里用 WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图（Q2)；

设置一个保存点，这个很重要（Q3）；

show create 是为了拿到表结构(Q4)，然后正式导数据 （Q5），回滚到SAVEPOINT sp，在这里的作用是释放 t1的MDL锁 （Q6。当然这部分属于“超纲”，上文正文里面都没提到。

DDL从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

  1. 如果在Q4语句执行之前到达，现象：没有影响，备份拿到的是DDL后的表结构。
  2. 如果在“时刻 2”到达，则表结构被改过，Q5执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump终止；
  3. 如果在“时刻2”和“时刻3”之间到达，mysqldump占着t1的MDL读锁，binlog被阻塞，现象：主从延迟，直到Q6执行完成。
  4. 从“时刻4”开始，mysqldump释放了MDL读锁，现象：没有影响，备份拿到的是DDL前的表结构。

7. 讲行锁功过：怎么减少行锁对性能的影响

行锁是在存储引擎级别实现的，并不是像全局锁和表级锁一样在 Server 端实现。

InnoDB 支持行锁，不支持列锁，并且 innodb 行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。因为不走索引，所以必须遍历整个表，那么就会读取一行，锁一行，又因为是两阶段锁，只有更新成功，事务提交之后才会释放锁。

MyISAM 最小的锁粒度是表，并且由于表锁的存在，同时刻只能是单个线程对表进行操作，这很大程度上限制了并发度，所以这也是 InnoDB 取代 MyISAM 的重要原因吧。

  1. 两阶段锁的概念是什么? 对事务使用有什么帮助?

两阶段锁是指行锁是在需要的时候加锁，但是并不是在不用了之后就释放锁，而是等到事务提交才释放锁，这一点跟 MDL （元数据锁）很像；

对事务的帮助就是：一定要将并发度高、时间较长的操作的行放到事务提交前，防止过多的线程等待锁。

  1. 为什么要使用两阶段锁协议，为何不在事务启动时就加锁，又为什么不在语句执行完就解锁呢？

首先回答一下为什么不在事务启动时就加锁，我觉得原因有两个：

  1. 减小锁的粒度和加锁时长，提高并发度。
  2. 事务启动的时候，并没有明确说明需要修改什么行，此时如果要锁定行必须锁定整个表才行。

再回答一下为什么不在语句执行完就解锁。

因为这样事务如果回滚的话，就会有问题了。例如下面这种情况：

image-20200325103514167

如果事务 A 回滚，那肯定无法回滚了，因为已经有多个事务修改了这个值，并且人家已经提交了，无法回滚。

  1. 死锁的概念是什么? 举例说明出现死锁的情况。

两个或多个线程在互相等待对方释放资源的过程称为死锁状态，出现死锁的情况一般都是嵌套锁（跟多线程中嵌套同步代码块一样）。

所以我的建议是在加锁的时候一般都做到顺序加锁，这样就能防止锁互相嵌套问题。

  1. 死锁的处理策略有哪两种?

在数据库中，死锁的处理策略有：

  • 有个超时机制，这个超时时间可以通过参数innodb_lock_wait_timeout来设置。时间到了自动释放锁，并且回滚当前事务，这个默认是 50s，时间太长，一般不考虑；
  • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。这个默认是开启的，但是吧，这个很消耗资源。
  1. 有哪些思路可以解决热点更新导致的并发问题?

热点更新导致的并发问题，症结就在于死锁检测非常的消耗资源，所以可以有以下解决措施：

  • 尽量避免死锁，比如上文说的加锁按顺序，然后直接将死锁检测关闭，但是这样容易造成业务有损，一般死锁检测虽然浪费资源但是是业务无损的，所以关闭可能不是特别可取；
  • 控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了；
  • 将热点细分，比如购买电影票，把影院的账户总额拆成10条记录来存储，其10条记录的和等于影院账户余额，这样能尽量减少死锁的情况。
  1. 什么时候会进行死锁检测？一旦触发死锁检测是检测所有事务吗？

  • 如果他要加锁访问的行上有锁，他才要检测。

  • 不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：

    B在等A，
    D在等C，
    现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A

今日面试题：

如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

  • 第一种，直接执行delete from T limit 10000;
  • 第二种，在一个连接中循环执行20次 delete from T limit 500;
  • 第三种，在20个连接中同时执行delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

在一个连接中循环执行20次 delete from T limit 500。

确实是这样的，第二种方式是相对较好的。

第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

确实是这样的，第二种方式是相对较好的。

第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

8. 事务到底是隔离还是不隔离的

这章知识点很多，难度比较大！

  1. 先回顾一下什么是 MVCC，如何实现的？

MVCC，多版本并发控制，在查询记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本。「存储在undo log 日志中」

img

  1. 什么是 read-view？

一致性视图，全称是 consistent read-view，这里稍微补充一下，在 mysql中有两种视图：

  • 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，而它的查询方法与表一样。
  • 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

在查询的时候，我们是需要用到 read-view的，但是在 update 的时候，是必须只能读当前最新值哦！！！「这里的最新值肯定是已经提交的事务去做的，如果未提交的事务去操作这个值，那肯定是加了行锁中的独占锁啦，所以你在 update 的话必须等其他事务释放锁。」

说了这么多，回到问题，read-view其实就是一个数组，每个事务或者语句都有的一个数组，只不过这个数组有点特殊。 InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

  1. 查询的时候，是如何用到 read-view 的？

InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图（read-view）确定数据版本的可见性。

  • 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；「因为对于可重复读的隔离级别，一致性视图是在事务开始的时候进行全库快照生成的」
  • 对于读提交，查询只承认在语句启动前就已经提交完成的数据；「因为对于读提交的隔离级别来说，一致性视图是在每一次执行语句的时候生成的，跟事务没有关系」

总结上述两种情况，就是只有视图生成前事务提交了的数据我能看到，其他的我都看不到。

而当前读，总是读取已经提交完成的最新版本。「第 2 个问题已经讲了这个了」

  1. 上面一直强调的，当前读，什么时候会出现当前读的情况？

首先我们要明确，当前读，必须要读的是该行的最新版本，此时的视图快照已经失效了，而且你要想读该行的最新版本，就必须去加锁！！！！「意思很明确，就是 当前读 == 给该行加锁」

所以，什么时候会出现行的当前读呢？那就是给该行加锁『悲观锁』了，那问题就转化成了什么时候会给行加锁呢？

那这个问题就非常简单了，有以下几种情况会使得当前行加上悲观锁：

  • SELECT … FROM … LOCK IN SHARE MODE，select 的时候强行加上共享锁，如果不加其实就是默认的快照读；
  • SELECT … FROM … FOR UPDATE，select 的时候强行加上独占锁；
  • UPDATE … WHERE … ，独占锁；
  • DELETE FROM … WHERE … ，独占锁；
  • INSERT 语句将在插入的记录上加一把排他锁「独占锁」。

好吧，其实也就是 select 啥都不加会导致不给行锁加悲观锁，其他的都会加的，意味着都是要去读当前值，意味着这个 rc 级别的隔离就无效了，不过话说回来，人家 rc 本身就是 可重复读 的隔离级别，本身就是用于 数据修改了我不去读 。

http://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html 这篇文章对 sql 语句加锁分析的我觉得是我看到的最好的了，受益匪浅！

  1. 第 4 个问题中提到的“lock in shared mode” “for update”是什么锁？

这个很简单啦，就是 mysql 中的悲观锁中的共享锁和独占锁。

  1. 既然谈到锁了，那你再回顾一下现在接触到的所有锁吧，也可以自己拓展一下！

其实，上面讲到的锁都是可以跟 lock 体系对应上的，比如说上文的乐观锁悲观锁，对应的就是 lock 体系的乐观锁悲观锁，乐观锁就是底层是 CAS，CAS 中有三个值：当前内存值、预读的值、想修改的值，如果当前值和预读值一样，我就默认没被修改过，直接修改成想修改的值，当然这样会造成一个问题：ABA 问题，在 mysql 中最主要的方法是加一个 version 字段，用 version 字段去判断是否被修改过，在 lock 体系中，最主要的方法是加一个 stamp，类似于时间戳这样的东西，在 StampedLock 里面有去用到。当然了，CAS 还会有另外两个问题：一个是自旋会造成cpu资源的浪费，一个是只能保证单个变量的原子性「你如果想保证多个的话就直接写到一个对象中就完事了」。

回到正题，上面讲完乐观锁，现在讲讲悲观锁吧，mysql 中的独占锁和共享锁和 AQS 中的完全一致，这两思想也完全一样，至于 mysql 中内部如何实现的我不太清楚，因为貌似是用 c 写的，看的兴趣不大， AQS 的底层源码是有看过的，AQS 就是用的模板方法的设计思想嘛，对外我们想要去实现读写锁的话只需要继承几个方法就可以了，比如独占锁 ReentrantLock 只需要继承 tryacquired()、tryRealease()、isHeldExclusive()…「说不定会让我手动去实现一个，这个我得注意一下，提前写一个，刚才写的过程中，又发现了 transient 这个关键词，真的牛皮，这个博客讲的真的好：https://juejin.im/post/5dda467051882572f8249f11#heading-10」。。。。懒得讲了。。。

9. 普通索引和唯一索引，应该如何选择

  1. 在查找过程中，普通索引和唯一索引的性能基本一样，但是在更新过程中，如果记录不在内存中，那普通索引和唯一索引的性能差距就很大了，因为普通索引可以使用 changebuffer，而唯一索引因为有唯一性约束，需要将所有数据读入到内存中，这样就失去了 changebuffer 的意义，所以速度很慢。
  2. changebuffer：存储用户更新的语句。在下次查询需要访问这个数据页的时候，才将数据页读入内存，执行 change buffer中与这个页有关的操作，注意哦，change buffer是可以持久化，将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。
  3. 故 changebuffer 适合写多读少的场景，这样能最大限度的加快写入的速度，所以说 changebuffer 可以避免写入的时候随机去读取数据到内存中，节省的是随机读磁盘的 IO 消耗。
  4. redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

tip: 为什么说 redo log 是将磁盘的随机写变成顺序写？

如果没有 redo log，那么每次数据来了我们都要直接去落盘，每一次数据我们都需要去进行磁盘随机读写，而有了 redo log我们可以先将数据顺序直接追加到文件末尾「redo log文件的大小是固定的，所以可以是顺序写」，然后redo log再在之后进行落盘，这样的话我们的数据就是相当于顺序写入磁盘了。

kafka 的顺序写其实和mysql 顺序写是一样的，都是采用了日志，kafka中使用 log，而 mysql 中采用 redo-log，数据来了先顺序追加到日志中，比如说日志是 4 g大小，那这 4 g 是一起落盘的，所以说这4g就是顺序写入磁盘的，因为他们是在一块的，不需要多次磁盘寻址写入。

这个写的还可以吧: 浅析MySQL事务中的redo与undo

10. Mysql 为什么会选错索引

  1. 选索引的任务，是交给了优化器去做的，而优化器选择索引的最优方案主要是看扫描行数「通过取样去判断，所以这块可能会造成误差」、临时表和排序等等；
  2. 如果选错了索引，我们可以强制使用 force index，然后如果更快的话，我们可以用 explain 拿到 rows 的大小，然后使用 analyze table t 命令重新统计索引信息，这样下次就不会选错了。

11. 怎么给字符串加索引

  1. 直接创建完整索引，但是这样比较占用空间；
  2. 创建前缀索引，节省空间，但是容易导致回表次数增多，并且无法使用范围查询；
  3. 倒序存储，主要用来解决字符串本身的前缀区分度不高，例如身份证号；
  4. 创建hash字段索引，查询稳定，但是不支持范围查询，同时因为要额外增加字段和索引，消耗资源。

12. 为什么 Mysql 会 ”抖“ 一下？

我们在第 9 节的内容其实跟这节也有点关系，因为都是有关于 redo log，我们在第 9 节谈到，redo log的出现，将我们随机写磁盘变为了顺序写磁盘「当然 redo log还有crash-safe的功能」，但是我们一直没有去谈写完 redo log之后数据，借此机会，我还是希望能够好好地回顾一下整个数据库更新的过程。

前提：假设我要将 id = 2 中的 c ＋ 1 ，步骤如下：

img

我们来细致分析一下在 写入 redo log，和提交事务分别发生了什么。

首先，我们需要知道的是，redo log 分为两部分：redo log buffer 和 redo log file，一个是在内存中，一个是在磁盘中，在写入 redo log 的时候，是先将数据写至 redo log buffer，然后再将 redo log buffer 中的数据顺序写入到redo log file，写完后处于 prepare 状态，在提交事务之前，就已经将 binlog 写入文件了，所以当提交事务的时候，redo log 和 binlog 全部持久化。

现在回到标题的问题。

  1. 什么叫”抖“一下？

一条SQL语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。可以认为是”抖“了一下。

  1. 为什么 Mysql 会经常抖一下？

根本原因是数据库此时在大量刷脏页

  1. 什么是脏页？

内存页与磁盘页不一致的页称为脏页

  1. 什么时候需要刷脏页？
  1. redo log 满了，需要将脏页落盘；
  2. 内存中 buffer pool 满了，需要将脏页落盘，变成干净页；
  3. Mysql 空闲，后台进行刷脏页；
  4. Mysql 正常关闭前，进行刷脏页。
  1. 这四种情况的刷脏页，对系统影响如何？
  1. redo log 写满再刷脏页，对系统影响很大，因为此时系统不能进行更新了，会阻塞；
  2. buffer pool 满了刷脏页，是很常见的，但是如果脏页多了，清理起来很慢；
  3. Mysql 空闲时刷脏页，对系统不会有啥性能上的影响；
  4. Mysql 正常关闭前刷脏页同样对系统不会有什么影响。
  1. 那应该如何避免刷脏页带来的性能影响呢？

控制脏页比例，刷盘速度不能过慢，可以通过 inno_io_capacity来控制速度，当然过快也没有必要，因为这样势必会消耗更多的资源导致用户使用资源变少了。

总结：虽然 redo log 可以带来 crash-safe 和将随机写变为顺序写这两大功能，但是其也带来了内存脏页的问题。

13. 为什么表数据删掉一半，表文件大小不变

  1. 表空洞问题
  1. delete 命令删除行是不能回收表空间的，只是当前页被标记为”可复用“，例如300 400 500，删除了400之后，400这个数据页就可以被复用，比如 401 就可以插入到其中，但是如果下一个是 800，那 400 这个数据页就只能被浪费了，成为空洞。
  2. 不止是删除数据会导致空洞，插入数据也会。当随机插入数据时，会造成索引的数据页分裂。例如当 300 500 600是一页的时候，此时来了个 550，那么就不得不申请一个新的页面来保存数据了，此时 550 会在新的数据页中，而 600 紧随其后，此时原来的页就只剩下了 300 500 了，这样就造成了数据页空洞。「这也是磁盘按页分配按页预读的坏处吧..」
  1. 如何解决表空洞问题？

重建表。例如表 A 存在空洞情况，可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序将数据一行一行的插入到 B 中，也就是将 B 变成临时表，最后将 A 替换即可。

直接使用 “alter table A engine=InnoDB”即可，系统会自动帮我们完成转存数据、交换表名、删除旧表的操作，而其中最慢的步骤就是往临时表插入数据的过程，在 MySQL 5.6版本之前，不能一边往临时表插入数据一边对原表 A 进行更新，但是在 MySQL 5.6 之后，可以进行 Online DDL。

  1. Online DDL 的流程？
  1. 建立一个临时文件，扫描 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件中，对应的是图中 state2 的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上和 A 相同的数据文件，对应的就是 state3 的状态；
  5. 用临时文件替换 A 的数据文件。

总结一下，就是用了一个 row log 来保证 Online 的。

img

  1. optimize table、analyze table、alter table这三种重建表的区别

Alter table 就是上面的步骤啦，转存数据、交换表名、删除旧表；

analyze table 其实不是重建表，只是对表的索引信息做一个重新统计，没有修改数据，比如之前修改优化器统计的遍历的 row 数；

optimize table 等于 recreate + analyze

14. count 这么慢，我该怎么办？

  1. 为什么 InnoDB 不像 MyISAM 一样，专门来记录 count 值？

因为 InnoDB 的隔离级别是 rr 级别，可重复读，所以在不同事务下，由于多版本并发控制的原因（MVCC），每一行记录都要判断自己是否对这个会话可现，因此对于 count(*) 来说，只好把数据一行一行的读出并进行判断。

  1. 那这种方法太蠢了，没有优化吗？

你都知道蠢了，当然就有优化啊，你想想，InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树上的叶子结点则是主键值，所以普通索引树比主键索引树肯定要小很多，而不管遍历哪个索引树其实结果都是一样的，所以 Mysql 会找到最小的那棵树进行遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库设计的通用法则之一。

  1. 那我们之前在第 10 节谈到的 ”show table status“ 命令中的 row numbers 能用吗？

当然不能啊，那只是一个预估值，官方说这个误差可以达到 40% ，所以肯定不行啊。

  1. 那如果我有一个总是要统总数的业务，该怎么办呢？

提出这个问题，说明你肯定觉得优化后的方法依旧很慢，是的，所以我们只能自己去操作。

把 count 这个计数直接放到数据库里单独的一张计数表 C 中，一是解决了崩溃丢失的问题，因为InnoDB支持崩溃恢复，二是解决了事务导致的无法精确统计count的问题，直接将表C中的计数值这个操作也加入到事务中就很okay了。

  1. count(字段)、count(主键id)、count(1)、count(*)的区别

count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

至于分析性能差别的时候，你可以记住这么几个原则：

  1. server层要什么就给什么；
  2. InnoDB只给必要的值；
  3. 现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

这是什么意思呢？接下来，我们就一个个地来看看。

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

  1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
  2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照count(*)来处理，多么简单的优化啊。

当然，MySQL专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且MySQL已经优化过count(*)了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。

15. 日志和索引相关问题

在第 2 讲中，有提到 redo log 和 binlog 的两阶段提交，这也是分布式系统常用的手段。

img

其中，时刻 A 如果 crash 了，此时还没写 binlog，所以崩溃恢复肯定就将 redo log 中的数据进行回滚就行了，时刻 B 情况，会稍微复杂一点，因为这个时候有三种情况：

  1. binlog 写完了并且redo log 已经有了 commit 标志，那这个时候肯定就直接提交就好了；
  2. binlog 没写完，redo log 此时只有 prepare 标志，那这个时候肯定得回滚了，因为binlog没写完；
  3. binlog有写完了的标志，但是 redo log 此时只有 prepare 标志，没问题，这个也可以直接提交。

追问：

  1. binlog 如何判断写完了？

一个事务的binlog是有完整格式的：

  • statement格式的binlog，最后会有COMMIT；
  • row格式的binlog，最后会有一个XID event。
  1. redo log 如何知晓binlog 写完了，然后将状态从 prepare 变为 commit，又是如何扫描 prepare or commit 标志，然后提交的呢？

它们有一个共同的数据字段，叫XID。binlog 写完后，有相应的通知机制，此时redo log 对应的 XID 状态变化。

崩溃恢复的时候，会按顺序扫描redo log：

  • 如果碰到既有prepare、又有commit的redo log，就直接提交；
  • 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。
  1. 只要binlog不可以吗，也可以提供顺序写磁盘的功能，如果 binlog 也加一个 crash-safe 的功能，那不是既能归档，又能崩溃恢复吗？

binlog存储的是逻辑日志，而且并没有标记读到哪了，并不能记录数据页的更改，所以不能做到崩溃恢复。

  1. 那能不能只要 redo log，不要 binlog？

从逻辑上可以，因为 redo log 除了不能归档，其他都能做。

但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是开着的。因为binlog有着redo log无法替代的功能。

一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。

一个就是MySQL系统依赖于binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是binlog复制。

还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新自己的数据。关掉binlog的话，这些下游系统就没法输入了。

总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log还做不到。你看，发展生态是多么重要。

  1. 数据的最终落盘，是从 redo log更新的还是从 buffer pool 更新的呢？

实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由redo log更新过去”的情况。

  1. 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系。
  2. 在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。
  3. 在我们第 12 讲的，如果 redo log 满了，也是要刷脏页的，此时会将 redo log对应的内存中的脏页刷入磁盘。

总而言之，其实 redo log 就是一个中间者，比如10 到 19，redo log 只是记录的 +9，所以肯定是从buffer pool 中更新的数据。

  1. redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

Redo log 是分为两块的：一块就是 redo log buffer，一块就是 redo log 文件，正常肯定是先到缓存，再到文件中啦。这里的写文件是一个顺序写的过程。等不忙的时候再执行 redo log 文件，进行最终的落盘。「也就是我们之前一直提到的刷脏页」

16. ”order by“ 是如何工作的？

  1. order by 是 Mysql 中是如何工作的？

有两种算法执行 order by。

  1. 全字段排序；
  2. rowid 排序。

科普：

MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size

  • 如果排序的数据量小于sort_buffer_size，排序将会在内存中完成如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
  • 在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件

mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序

  • 如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
  • 该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
  • 按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表

全字段排序过程：

  1. 通过索引将所需的字段全部读取到sort_buffer中

  2. 按照排序字段进行排序

  3. 将结果集返回给客户端

优点： MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

缺点：

  1. 造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
  2. 当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

rowid 排序

  1. 通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data

  2. 只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序

  3. 按照排序后的顺序，取id进行回表取出想要获取的数据
  4. 将结果集返回给客户端

优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

  1. 两种算法的应用场景？

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。

这个结论看上去有点废话的感觉，但是你要记住它，下一篇文章我们就会用到。

看到这里，你就了解了，MySQL做排序是一个成本比较高的操作。那么你会问，是不是所有的order by都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

  1. 那如果原来的数据就是有序的，是不是就可以不用排序了？如何做到有序呢？

是的，如果数据本身就是有序的，就无须排序了。

这里，我们就可以采用覆盖索引，因为索引本身就是有序的，这样就不需要额外排序带来系统的消耗了，但是同样的维护索引也是有维护代价的。

17. 如何正确地显示随机消息

MySQL对临时表排序的执行过程。

如果你直接使用order by rand()，这个语句需要Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要尽量避开这种写法。

18. 为什么这些SQL语句逻辑相同，性能却差异巨大

  1. 条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

会遍历全表，所以很慢…

  1. 隐式类型转换

mysql> select * from tradelog where tradeid=110717;

例如上面这条语句， tradeid 是 varchar 类型，但是传进来整数型，这个时候就会类型转换，导致不能走索引了。

其实类型转换就是相当于函数操作了，所以会走全表而不是走索引。

  1. 隐式字符编码转换

字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

总而言之，这些 sql 语句之所以执行慢，就是因为加了在条件中加了函数操作。

19. 为什么我只查一行的语句，也执行这么慢

方法：执行一下show processlist命令，看看当前语句处于什么状态。

原因可能有：

第一类：查询一条数据长时间不返回

  1. 等 MDL 锁。使用show processlist命令，发现是 Waiting for table metadata lock。出现这个状态表示的是，现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。
  2. 等行锁。

第二类：查询慢

一致性读的问题，需要不断回滚undo log，所以慢。这里因为只是查一行，所以不能算是慢查询，只能算坏查询，等数据量上去了，就变成慢查询了。

20. 幻读是什么，幻读有什么问题？「未认真研读」

  1. 幻读是什么？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。「加行锁读的时候才会出现，破坏mvcc，例如update、insert」
  2. 幻读仅专指“新插入的行”。
  1. 幻读有什么问题？
  1. 首先是语义上有问题。session A在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
  2. 数据一致性问题。

即使把所有的记录都加上锁，还是阻止不了新插入的记录

  1. 那这么难对付，Mysql 是如何解决呢？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

间隙锁和next-key lock的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

21. 为什么我只改一行的语句，锁这么多「未研读」

有点难…有时间再研读

22. MySQL有哪些“饮鸩止渴”提高性能的方法

  1. 面对短连接风暴时，如何解决？

第一种方法：先处理掉那些占着连接但是不工作的线程。

第二种方法：减少连接过程的消耗。有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables参数启动。这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

  1. 慢查询问题如何解决？

在MySQL中，会引发性能问题的慢查询，大体有以下三种可能：

  1. 索引没有设计好；

  2. SQL语句没写好；

  3. MySQL选错了索引。

导致慢查询的第一种可能是，索引没有设计好。这种场景一般就是通过紧急创建索引来解决。MySQL 5.6版本以后，创建索引都支持Online DDL了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的大致流程是这样的：

  1. 在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；
  2. 执行主备切换；
  3. 这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引。

这是一个“古老”的DDL方案。平时在做变更的时候，你应该考虑类似gh-ost这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

导致慢查询的第二种可能是，语句没写好。

这时，我们可以通过改写SQL语句来处理。MySQL 5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。

比如，语句被错误地写成了 select * from t where id + 1 = 10000，你可以通过下面的方式，增加一个语句改写规则。

1
2
3
4
> mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
> 
> call query_rewrite.flush_rewrite_rules();
>

这里，call query_rewrite.flush_rewrite_rules()这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。

导致慢查询的第三种可能，就是MySQL选错了索引。

这时候，应急方案就是给这个语句加上force index。

  1. 如何避免慢查询导致性能问题？

上面我和你讨论的由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，我们就可以预先发现问题。

  1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；
  2. 在测试表里插入模拟线上的数据，做一遍回归测试；
  3. 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。

23 - 28 讲

MySQL第24-28讲-2

后续章节暂时感觉没有太多看的必要，最后以一道题结束本篇文章。

image-20200404162344799

我们假设原来的大表有10000行数据，而符合条件的数据有100行，我们最后的结果集是只要10行

我们知道，limit是一个语句最后执行的，也就是说会先把符合条件的数据全部找到，最后再进行 limit。

在第一种情况中，会先去非聚集索引中找符合条件的主键值，然后进行回表，我们可以知道，会遍历大表10000行，回表100次，最后数据全部拿到之后，会进行 limit；

而在第二种情况，会直接先去非聚集索引中找符合条件的主键值，这一步和第一种情况是一致的，也是会遍历大表10000行，但是注意，这里在内部有进行 limit，也就是说，找到了符合条件的主键值，就直接 limit了，这样就会只剩下 10 个有效的主键值，然后再去回表，也是相当于只会回表十次。所以应该快的原因在这了….一个回表100次，一个回表 10 次，这还只是在符合条件的数据只有100行的情况下，如果符合条件的数据是1000000行，那这个回表的次数减少的可就很恐怖了。。。

一开始自己的想法以为是 right join 带来的性能的巨大提升，后面发现并不是，但是也顺带看了一遍 join 提升性能的一些方法。

「第34、35讲」

  1. 第一个问题：能不能使用join语句？

    1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
    2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

    所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

    第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

    1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
    2. 如果是Block Nested-Loop Join算法：
      • 在join_buffer_size足够大的时候，是一样的；
      • 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

    所以，这个问题的结论就是，总是应该使用小表做驱动表。

总结

后续其实还有10多节未看，但是稍微略读了下，感觉可读性不是很强了，毕竟不是专业 DBA，暂时 MySQL 告一段落了…

Thank you for your accept. mua！
-------------本文结束感谢您的阅读-------------